精通pandas：数据透视表

数据透视表提供了一种快速汇总大量数据的方法。使用pandas和xlwings都可以创建数据透视表。[大谦Excel，dqexcel点com]

创建数据透视表

【问题描述】

给定一维表数据，创建数据透视表。

【示例10-9】

本例使用的Excel文件的完整路径为“D:/Samples/ch10/06 透视表/蔬果信息.xlsx”。该文件打开后如图10-6所示，是各种产品的采购信息。要求创建数据透视表，其中“产品”为列字段，“产地”为行字段，“金额”为值字段。

图10-6 产品采购信息

编写下面的代码：

code.python

import pandas as pd
# 读取Excel文件中的数据，指定引擎为openpyxl
df = pd.read_excel('D:/Samples/ch10/06 透视表/蔬果信息.xlsx', engine='openpyxl')
# 使用pandas的pivot_table()方法生成数据透视表，其中"产品"为列字段，"产地"为行字段，"金额"为值字段
pivot_table = pd.pivot_table(df, values='金额', index=['产地'], columns=['产品'])
# 输出数据透视表的内容
print(pivot_table)

打开Python IDLE，新建一个脚本文件，将上面生成的代码复制进去，保存。运行脚本，在IDLE Shell窗口输出数据透视表。

code.python

>>> == RESTART: D:/Samples/1.py =
产品             橙子          胡萝卜      芒果  ...          西兰花           豆荚           香蕉
产地                                      ...
加拿大   6643.000000          NaN  3767.0  ...  4135.666667          NaN  4825.000000
德国    8887.000000  3606.000000  8775.0  ...  4649.625000  4984.166667  4409.555556
新西兰   4003.333333          NaN     NaN  ...  4390.000000          NaN  5006.250000
法国    2256.000000  9104.000000  7388.0  ...  5341.000000   680.000000  5156.285714
澳大利亚  2893.333333  2702.000000  4593.0  ...  8976.500000  4810.000000  5272.100000
美国    3866.500000  5628.400000  4472.6  ...  6678.750000  7163.000000  4133.086957
英国    4348.800000  5973.571429  5600.0  ...  4804.500000  2550.000000  6129.714286
[7 rows x 7 columns]

默认时，数据透视表中的值保留6位小数。

【知识点扩展】

用pandas的pivot_table函数创建数据透视表。用第1个参数指定DataFrame对象，用values参数指定值字段，用index参数指定行字段，用columns参数指定列字段，用aggfunc参数指定聚合函数。

设置数据透视表中值的输出格式

【问题描述】

指定生成的数据透视表中的值的输出格式，例如保留2位小数。

【示例10-10】

本例使用示例10-9的数据，要求创建数据透视表，其中“产品”为列字段，“产地”为行字段，“金额”为值字段。数据透视表中的值保留2位小数。

编写下面的代码：

code.python

import pandas as pd
# 读入Excel文件中第一个工作表的数据，引擎为openpyxl
df = pd.read_excel('D:/Samples/ch10/06 透视表/蔬果信息.xlsx', engine='openpyxl')
# 生成数据透视表，行字段为“产地”，列字段为“产品”，值字段为“金额”
pivot_table = pd.pivot_table(df, values='金额', index=['产地'], columns=['产品'])
# 使用applymap方法设置金额保留2位小数
pivot_table = pivot_table.applymap('{:.2f}'.format)
# 输出数据透视表
print(pivot_table)

打开Python IDLE，新建一个脚本文件，将上面生成的代码复制进去，保存。运行脚本，在IDLE Shell窗口输出设置输出格式后的数据透视表。

code.python

>>> == RESTART: D:/Samples/1.py =
产品         橙子      胡萝卜       芒果       苹果      西兰花       豆荚       香蕉
产地
加拿大   6643.00      nan  3767.00  4144.50  4135.67      nan  4825.00
德国    8887.00  3606.00  8775.00  4541.00  4649.62  4984.17  4409.56
新西兰   4003.33      nan      nan  5166.00  4390.00      nan  5006.25
法国    2256.00  9104.00  7388.00  5012.06  5341.00   680.00  5156.29
澳大利亚  2893.33  2702.00  4593.00  5158.50  8976.50  4810.00  5272.10
美国    3866.50  5628.40  4472.60  4769.17  6678.75  7163.00  4133.09
英国    4348.80  5973.57  5600.00  4383.50  4804.50  2550.00  6129.71

现在数据透视表中的值都设置成了保留2位小数的格式。

处理缺失值

【问题描述】

如果生成的数据透视表中存在缺失值，对数据透视表进行某些设置时可能会出错，所以需要对缺失值进行处理。

【示例10-11】

本例使用示例10-9的数据，要求创建数据透视表，其中“产品”为列字段，“产地”为行字段，“金额”为值字段。数据透视表中的值设置为整数。

编写下面的代码：

code.python

import pandas as pd
# 读取Excel文件中第一个工作表的A-F列数据到DataFrame中
df = pd.read_excel('D:/Samples/ch10/06 透视表/蔬果信息.xlsx', engine='openpyxl', usecols='A:F')
# 用pivot_table方法生成数据透视表，其中“产品”为列字段，“产地”为行字段，“金额”为值字段
pivot_table = pd.pivot_table(df, values='金额', index='产地', columns='产品')
# 用applymap方法设置金额为整数
pivot_table = pivot_table.applymap(int)
# 输出表数据
print(pivot_table)

打开Python IDLE，新建一个脚本文件，将上面生成的代码复制进去，保存。运行脚本，在IDLE Shell窗口输出下面的出错信息。

code.python

>>> == RESTART: D:/Samples/1.py =
Traceback(most recent call last):
  File "C:/Users/Administrator/AppData/Local/Programs/Python/Python37/1.py", line 10, in <module>
    pivot_table = pivot_table.applymap(int)
  ……
ValueError: cannot convert float NaN to integer

【示例10-12】

本例使用示例10-9的数据，要求创建数据透视表，其中“产品”为列字段，“产地”为行字段，“金额”为值字段。用0填充数据透视表中的缺失值。数据透视表中的值设置为整数。

编写下面的代码：

code.python

import pandas as pd
# 读取Excel文件并指定引擎为"openpyxl"
df = pd.read_excel("D:/Samples/ch10/06 透视表/蔬果信息.xlsx", engine="openpyxl")
# 生成数据透视表
pivot_table = pd.pivot_table(df,
                             values='金额',  # 值字段为“金额”
                             index='产地&#x27;,    # 行字段为“产地”
                             columns='产品',  # 列字段为“产品”
                             fill_value=0)    # 缺失值填充为0
# 使用applymap方法设置金额为整数
pivot_table = pivot_table.applymap(int)
# 输出透视表数据
print(pivot_table)

打开Python IDLE，新建一个脚本文件，将上面生成的代码复制进去，保存。运行脚本，在IDLE Shell窗口输出下面的设置效果。

code.python

>>> == RESTART: D:/Samples/1.py =
产品      橙子   胡萝卜    芒果    苹果   西兰花    豆荚    香蕉
产地
加拿大   6643     0  3767  4144  4135     0  4825
德国    8887  3606  8775  4541  4649  4984  4409
新西兰   4003     0     0  5166  4390     0  5006
法国    2256  9104  7388  5012  5341   680  5156
澳大利亚  2893  2702  4593  5158  8976  4811  5272
美国    3866  5628  4472  4769  6678  7163  4133
英国    4348  5973  5600  4383  4804  2550  6129

设置数据透视表的聚合函数

【问题描述】

pandas创建数据透视表时，默认时使用的聚合函数是求均值，可以通过pivot_table方法的aggfunc参数将聚合函数设置为其他函数，如求和、求次数、求方差等。

【示例10-13】

本例使用示例10-9的数据，要求创建数据透视表，其中“产品”为列字段，“产地”为行字段，“金额”为值字段。聚合函数为求和。

编写下面的代码：

code.python

import pandas as pd
# 读入 Excel 文件
file_path = 'D:/Samples/ch10/06 透视表/蔬果信息.xlsx'
df = pd.read_excel(file_path, engine='openpyxl')
# 生成数据透视表
pivot_table = pd.pivot_table(df,
                             values='金额', # 值字段为“金额”
                             index='产地', # 行字段为“产地”
                             columns='产品', # 列字段为“产品”
                             aggfunc=sum, # 聚合函数为求和
                             fill_value=0) # 缺失值用0填充
# 取整数
pivot_table = pivot_table.applymap(int)
# 打印数据透视表
print(pivot_table)

打开Python IDLE，新建一个脚本文件，将上面生成的代码复制进去，保存。运行脚本，在IDLE Shell窗口输出聚合函数为求和得到的数据透视表。

code.python

>>> == RESTART: D:/Samples/1.py =
产品       橙子    胡萝卜     芒果     苹果    西兰花     豆荚     香蕉
产地
加拿大   19929      0   3767  24867  12407      0  33775
德国     8887  21636   8775   9082  37197  29905  39686
新西兰   12010      0      0  10332   4390      0  40050
法国     2256   9104   7388  80193   5341    680  36094
澳大利亚   8680   8106   9186  20634  17953  14433  52721
美国    30932  56284  22363  28615  26715   7163  95061
英国    21744  41815   5600  17534  38436   5100  42908

【知识点扩展】

可用的聚合函数可以是Series对象的统计类方法，如sum, mode, std, var等，用的时候用引号引起来，也可以是NumPy的统计类函数，如np.sum, np.mean等。

可以同时指定多个聚合函数，放在列表中指定给aggfunc参数即可。

【示例10-14】

本例使用示例10-9的数据，要求创建数据透视表，其中“产品”为列字段，“产地”为行字段，“金额”为值字段。聚合函数为求次数，即同一产地和产品的数据条目个数。

编写下面的代码：

code.python

import pandas as pd
# 读取Excel文件并选择第一个工作表
df = pd.read_excel('D:/Samples/ch10/06 透视表/蔬果信息.xlsx', engine='openpyxl', sheet_name=0)
# 生成透视表，指定"产品"为列字段，"产地"为行字段，"金额"为值字段，聚合函数为计数
pivot_table = pd.pivot_table(df, values='金额', index='产地', columns='产品', aggfunc= 'count', fill_value=0)
# 输出结果
print(pivot_table)

打开Python IDLE，新建一个脚本文件，将上面生成的代码复制进去，保存。运行脚本，在IDLE Shell窗口输出聚合函数为求次数的数据透视表。

code.python

>>> == RESTART: D:/Samples/1.py =
产品    橙子  胡萝卜  芒果  苹果  西兰花  豆荚  香蕉
产地
加拿大    3    0   1   6    3   0   7
德国     1    6   1   2    8   6   9
新西兰    3    0   0   2    1   0   8
法国     1    1   1  16    1   1   7
澳大利亚   3    3   2   4    2   3  10
美国     8   10   5   6    4   1  23
英国     5    7   1   4    8   2   7

给数据透视表添加行、列汇总

【问题描述】

给生成的数据透视表添加行汇总和列汇总。

【示例10-15】

本例使用示例10-9的数据，要求创建数据透视表，其中“产品”为列字段，“产地”为行字段，“金额”为值字段。聚合函数为求和，添加行、列汇总。

编写下面的代码：

code.python

import pandas as pd
# 读取Excel文件
df = pd.read_excel('D:/Samples/ch10/06 透视表/蔬果信息.xlsx', engine='openpyxl')
# 生成数据透视表
pivot_table = pd.pivot_table(df,
                             values='金额&#x27;,    # 值字段为“金额”
                             index='产地&#x27;,     # 行字段为“产地”
                             columns='产品',   # 列字段为“产品”
                             aggfunc=sum,      # 聚合函数为求和
                             fill_value=0,     # 缺失值用0填充
                             margins=True,     # 添加行、列汇总
                             margins_name=';总计' # 汇总行、列名
                            )
# 对透视表的值取整数
pivot_table = pivot_table.astype(int)
# 打印结果
print(pivot_table)

打开Python IDLE，新建一个脚本文件，将上面生成的代码复制进去，保存。运行脚本，在IDLE Shell窗口输出带行汇总和列汇总的数据透视表。

code.python

>>> == RESTART: D:/Samples/1.py =
产品        橙子     胡萝卜     芒果      苹果     西兰花     豆荚      香蕉       总计
产地
加拿大    19929       0   3767   24867   12407      0   33775    94745
德国      8887   21636   8775    9082   37197  29905   39686   155168
新西兰    12010       0      0   10332    4390      0   40050    66782
法国      2256    9104   7388   80193    5341    680   36094   141056
澳大利亚    8680    8106   9186   20634   17953  14433   52721   131713
美国     30932   56284  22363   28615   26715   7163   95061   267133
英国     21744   41815   5600   17534   38436   5100   42908   173137
总计    104438  136945  57079  191257  142439  57281  340295  1029734

【知识点扩展】

将pivot_table函数中margins参数的值设置为True，即可实现给数据透视表添加行汇总和列汇总。用margins_name参数可以设置行、列总结的名称。

设置数据透视表中数据的显示方式

【问题描述】

生成数据透视表后，设置数据透视表中数据的显示方式，比如让数据透视表中的值按百分数表示。

【示例10-16】

本例使用示例10-9的数据，要求创建数据透视表，其中“产品”为列字段，“产地”为行字段，“金额”为值字段。数据透视表中每行数据显示为占行总计的百分数，如10.00%。添加“总计”列，该列的值均为100.00%。

编写下面的代码：

code.python

import pandas as pd
# 读取Excel文件
df = pd.read_excel("D:/Samples/ch10/06 透视表/蔬果信息.xlsx", engine="openpyxl")
# 生成数据透视表
pivot_table = pd.pivot_table(df, values='金额', index='产地', columns='产品', aggfunc='sum', fill_value=0, margins=False)
# 将每个数据显示为该数据占所在行行总计的百分数，保留2位小数
pivot_table = pivot_table.apply(lambda x: x/x.sum()*100, axis=1).round(2)
# 添加“总计”列，值均为100
pivot_table['总计'] = 100
# 以100%的形式显示所有数据
pivot_table = pivot_table.applymap(lambda x: str(x) + '%')
# 输出表数据
print(pivot_table)

打开Python IDLE，新建一个脚本文件，将上面生成的代码复制进去，保存。运行脚本，在IDLE Shell窗口输出设置后的数据透视表。

code.python

>>> == RESTART: D:/Samples/1.py =
产品        橙子     胡萝卜     芒果      苹果     西兰花      豆荚      香蕉   总计
产地
加拿大   21.03%    0.0%  3.98%  26.25%   13.1%    0.0%  35.65%  100%
德国     5.73%  13.94%  5.66%   5.85%  23.97%  19.27%  25.58%  100%
新西兰   17.98%    0.0%   0.0%  15.47%   6.57%    0.0%  59.97%  100%
法国      1.6%   6.45%  5.24%  56.85%   3.79%   0.48%  25.59%  100%
澳大利亚   6.59%   6.15%  6.97%  15.67%  13.63%  10.96%  40.03%  100%
美国    10.58%  21.07%  8.37%  10.71%   10.0%   2.68%  35.59%  100%
英国    12.56%  24.15%  3.23%  10.13%   22.2%   2.95%  24.78%  100%

【知识点扩展】

本例中是将行数据显示为占行总计的百分数，如果是将列数据显示为占列总计的百分数呢？将代码修改以下，如下所示。

code.python

import pandas as pd
# 读取Excel文件
df = pd.read_excel("D:/Samples/ch10/06 透视表/蔬果信息.xlsx", engine="openpyxl")
# 生成数据透视表
pivot_table = pd.pivot_table(df, values='金额', index='产地', columns='产品', aggfunc='sum', fill_value=0, margins=False)
# 将每个数据显示为该数据占所在列列总计的百分数，保留2位小数
pivot_table = pivot_table.apply(lambda x: x/x.sum()*100, axis=0).round(2)
# 添加“总计”行，值均为100
pivot_table.loc['总计'] = 100
# 以100%的形式显示所有数据
pivot_table = pivot_table.applymap(lambda x: str(x) + '%')
# 输出表数据
print(pivot_table)

打开Python IDLE，新建一个脚本文件，将上面生成的代码复制进去，保存。运行脚本，在IDLE Shell窗口输出设置后的数据透视表。

code.python

>>> == RESTART: D:/Samples/1.py =
产品        橙子     胡萝卜      芒果      苹果     西兰花      豆荚      香蕉
产地
加拿大   19.08%    0.0%    6.6%   13.0%   8.71%    0.0%   9.93%
德国     8.51%   15.8%  15.37%   4.75%  26.11%  52.21%  10.66%
新西兰    10.5%    0.0%    0.0%    5.4%   3.08%    0.0%  10.77%
法国     2.16%   6.65%  12.94%  41.93%   3.75%   1.19%  10.61%
澳大利亚   8.31%   5.92%  16.09%  10.79%   12.6%   25.2%  15.49%
美国    29.62%   41.1%  39.18%  14.96%  18.76%  12.51%  27.93%
英国    20.82%  30.53%   9.81%   9.17%  26.98%    8.9%  12.61%
总计    100.0%  100.0%  100.0%  100.0%  100.0%  100.0%  100.0%

对数据透视表进行排序

【问题描述】

生成数据透视表后，对数据透视表中的数据进行排序。

【示例10-17】

本例使用示例10-9的数据，要求创建数据透视表，其中“产品”为列字段，“产地”为行字段，“金额”为值字段。按照行汇总数据进行升序排列。

编写下面的代码：

code.python

import pandas as pd
# 读取Excel文件，指定引擎为openpyxl
df = pd.read_excel('D:/Samples/ch10/06 透视表/蔬果信息.xlsx', engine='openpyxl')
# 对数据进行透视操作
pivot_table = pd.pivot_table(df,
                             values='金额',
                             index='产地',
                             columns='产品',
                             aggfunc='sum',
                             margins=True,  # 添加行、列汇总
                             margins_name='总计')  # 汇总名称为“总计”
# 使用applymap方法设置保留2位小数
pivot_table = pivot_table.applymap(int)
# 按行进行升序排列
pivot_table = pivot_table.sort_values(by='总计', ascending=True)
# 输出结果
print(pivot_table)

打开Python IDLE，新建一个脚本文件，将上面生成的代码复制进去，保存。运行脚本，在IDLE Shell窗口输出排序后的数据透视表。

code.python

>>> == RESTART: D:/Samples/1.py =
产品        橙子     胡萝卜     芒果      苹果     西兰花     豆荚      香蕉       总计
产地
新西兰    12010       0      0   10332    4390      0   40050    66782
加拿大    19929       0   3767   24867   12407      0   33775    94745
澳大利亚    8680    8106   9186   20634   17953  14433   52721   131713
法国      2256    9104   7388   80193    5341    680   36094   141056
德国      8887   21636   8775    9082   37197  29905   39686   155168
英国     21744   41815   5600   17534   38436   5100   42908   173137
美国     30932   56284  22363   28615   26715   7163   95061   267133
总计    104438  136945  57079  191257  142439  57281  340295  1029734

【知识点扩展】

使用DataFrame对象的sort_values方法进行排序即可，用by参数指定作为排序依据的列数据，用ascending参数指定排序的方向。

聚合函数为连接字符串

【问题描述】

创建数据透视表时，值为用指定连接符连接的字符串。

【示例10-18】

本例使用的Excel文件的完整路径为“D:/Samples/ch10/06 透视表/找老乡.xlsx”。该文件打开后如图10-9所示，是一些人员的籍贯信息。要求根据该数据找出哪些人是老乡。

图10-9 找老乡

编写下面的代码：

code.python

# 导入 pandas 库
import pandas as pd
# 读取 Excel 文件，指定引擎为 "openpyxl"
df = pd.read_excel('D:/Samples/ch10/06 透视表/找老乡.xlsx', engine='openpyxl')
# 生成数据透视表，其中“籍贯”为行字段，值为将同一省份的姓名用"、"连接得到的字符串
pivot_table = df.pivot_table(values='姓名', index='籍贯', aggfunc=lambda x: '、'.join(x))
# 打印输出数据透视表
print(pivot_table)

打开Python IDLE，新建一个脚本文件，将上面生成的代码复制进去，保存。运行脚本，在IDLE Shell窗口输出处理结果。

code.python

>>> == RESTART: D:/Samples/1.py =
          姓名
籍贯
四川        马骏
山西        李四
河北  张三、林秀、韩当
湖南     王五、汪雨
福建        苏文

【示例10-19】

本例使用的Excel文件的完整路径为“D:/Samples/ch10/06 透视表/球员奖项汇总.xlsx”。该文件打开后如图10-10所示，是获得不同奖项的球员的名单。该数据不是一维表，要求将数据转换为一维表形式，然后用数据透视表得到每个球员获得的所有奖项。

图10-10 获得奖项的球员名单

编写下面的代码：

code.python

import pandas as pd
# 读取Excel文件中第一个工作表的前5行，前3列数据
df = pd.read_excel('D:/Samples/ch10/06 透视表/球员奖项汇总.xlsx', engine='openpyxl', sheet_name=0, header=0, nrows=5, usecols=[0,1,2])
# 将数据转换为一维表，重命名列名为“奖项”和“球员”
melted_df = df.melt(var_name='奖项', value_name='球员')
# 创建数据透视表
pivot_df = melted_df.pivot_table(index='球员', values='奖项', aggfunc=lambda x: '、'.join(x))
print(pivot_df)

打开Python IDLE，新建一个脚本文件，将上面生成的代码复制进去，保存。运行脚本，在IDLE Shell窗口输出

code.python

>>> == RESTART: D:/Samples/1.py =
                奖项
球员
C罗        金球奖、最佳球员
伊涅斯塔       金球奖、金靴奖
内马尔           最佳球员
哈维             金球奖
姆巴佩        金球奖、金靴奖
梅西    金球奖、最佳球员、金靴奖
苏亚雷斯           金靴奖

【知识点扩展】

本例给定的数据是二维表数据，创建数据透视表时需要数据源为一维表数据，所以需要先用DataFrame对象的melt方法将数据转换为一维表数据。

本页目录

创建数据透视表

【问题描述】

【示例10-9】

【知识点扩展】

设置数据透视表中值的输出格式

【问题描述】

【示例10-10】

处理缺失值

【问题描述】

【示例10-11】

【示例10-12】

设置数据透视表的聚合函数

【问题描述】

【示例10-13】

【知识点扩展】

【示例10-14】

给数据透视表添加行、列汇总

【问题描述】

【示例10-15】

【知识点扩展】

设置数据透视表中数据的显示方式

【问题描述】

【示例10-16】

【知识点扩展】

对数据透视表进行排序

【问题描述】

【示例10-17】

【知识点扩展】

聚合函数为连接字符串

【问题描述】

【示例10-18】

【示例10-19】

【知识点扩展】